<?php
class AdminUser extends CActiveRecord{

    public static function model($className = __CLASS__) {
        return parent::model ( $className );
    }

    // dat luat cho model
    public function rules(){}         

    // goi den bang can ket noi   
    public function tableName() {                  
        return 'user_veso';
    }

    // nhan cac thuong tinh 
    public function attributeLabels(){}

    public function getRowsByUser(){        
        $row = array();
        $sql = "SELECT `id`, `username`, `status`, `password`, `fullname`, `email`, `mobile`, `address`, `tk_phu`, `tk_chinh`, `create_user`, DATE_FORMAT(`create_date`,'%d/%m/%Y') as `create_date`, `modify_user`, `modify_date` FROM `user_veso` order by id desc";
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);
        $row = $command->queryAll();
        return $row;
    }

    public function getRowsByTitleUser($title,$status_user,$tungay,$denngay){
        $row = array();        
        $str .= " `username` LIKE '%$title%'";                
        if($status_user != 3){
            $str .= " AND `status` = '$status_user'";    
        }
        if($tungay != ""){
            $str .= " AND create_date > '$tungay'";    
        }
        if($denngay != ""){
            $str .= " AND create_date < '$denngay'";    
        }
        $sql = "SELECT * FROM user_veso WHERE ".$str;
        
        
        
        /*if($status_user !=3){
            $sql = "SELECT * FROM `user_veso` WHERE `username` LIKE '%".$title."%' AND `status` = $status_user order by id desc";
        }else{
            $sql = "SELECT * FROM `user_veso` WHERE `username` LIKE '%".$title."%' order by id desc";   
        }*/        
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);            
        $row = $command->queryAll();                        
        return $row;
    }

    public function getRowsById($id){

        $row = array();        
        $sql = "SELECT `id`, `username`, `status`, `password`, `fullname`, `email`, `mobile`, `address`, `tk_phu`, `tk_chinh`, `create_user`, DATE_FORMAT(`create_date`,'%d/%m/%Y') as `create_date`, `modify_user`, `modify_date` FROM `user_veso` WHERE `id` = '".$id."'";
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);            
        $row = $command->queryAll();                        
        return $row;
    }

    public function getRowsByUserPaging($begin,$end){        
        $row = array();        
        $sql = "SELECT `id`, `username`, `status`, `password`, `fullname`, `email`, `mobile`, `address`, `tk_phu`, `tk_chinh`, `create_user`, DATE_FORMAT(`create_date`,'%d/%m/%Y') as `create_date`, `modify_user`, `modify_date` FROM `user_veso` ORDER BY id DESC LIMIT ".$begin.",".$end;            
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);
        $row = $command->queryAll();            
        return $row;
    }

    public function getRowsByTitleUserPaging($title,$status_user,$tungay,$denngay,$begin,$end){        
        $row = array();     
         $str .= " `username` LIKE '%$title%'";                
        if($status_user != 3){
            $str .= " AND `status` = '$status_user'";    
        }
        if($tungay != ""){
            $str .= " AND create_date > '$tungay'";    
        }
        if($denngay != ""){
            $str .= " AND create_date < '$denngay'";    
        }
        $str .= " ORDER BY id DESC LIMIT ".$begin.",".$end;
        $sql = "SELECT `id`, `username`, `status`, `password`, `fullname`, `email`, `mobile`, `address`, `tk_phu`, `tk_chinh`, `create_user`, DATE_FORMAT(`create_date`,'%d/%m/%Y') as `create_date`, `modify_user`, `modify_date` FROM user_veso WHERE ".$str;
        
        
         
        /*if($status_user !=3){
            $sql = "SELECT `id`, `username`, `status`, `password`, `fullname`, `email`, `mobile`, `address`, `tk_phu`, `tk_chinh`, `create_user`, DATE_FORMAT(`create_date`,'%d/%m/%Y') as `create_date`, `modify_user`, `modify_date` FROM `user_veso` WHERE `username` LIKE '%".$title."%' AND `status` = $status_user ORDER BY id DESC   LIMIT ".$begin.",".$end;    
        }else{
            $sql = "SELECT `id`, `username`, `status`, `password`, `fullname`, `email`, `mobile`, `address`, `tk_phu`, `tk_chinh`, `create_user`, DATE_FORMAT(`create_date`,'%d/%m/%Y') as `create_date`, `modify_user`, `modify_date` FROM `user_veso` WHERE `username` LIKE '%".$title."%' ORDER BY id DESC   LIMIT ".$begin.",".$end;    
        }*/          
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);            
        $row = $command->queryAll();                        
        return $row;
    }

    public function deleteRowById($id)
    {
        $row = array();                        
        $sql = "DELETE FROM `user_veso` WHERE `user_veso`.`id` = ".$id." ";
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);
        $row = $command->execute();            
        return $row;
    }

    public function updateRowById($id,$username,$fullname,$email,$mobile,$address,$tk_phu,$tk_chinh)
    {
        $date = date('Y-m-d',time());
        $row = array();                        
        $sql = "UPDATE `user_veso` SET `username` = '".$username."',`modify_date` = '".$date."', `fullname` = '".$fullname."',`email` = '".$email."',`mobile` = '".$mobile."',`address` = '".$address."',`tk_phu` = '".$tk_phu."',`tk_chinh` = '".$tk_chinh."' WHERE `user_veso`.`id` = ".$id." ";
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);
        $row = $command->execute();            
        return $row;
    }
    
    public function insertRow($username,$fullname,$email,$mobile,$address,$tk_phu,$password)
    {        
        $date = date('Y-m-d',time());
        $row = array();              
        $sql = "INSERT INTO `user_veso` (`username`, `fullname`, `create_date`,`email`,`mobile`,`address`,`tk_phu`,`password`) VALUES ('".$username."','".$fullname."','".$date."','".$email."','".$mobile."','".$address."','".$tk_phu."','".$password."')";        
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);
        $row = $command->execute();            
        return $row;
    }
    
    public function updateStatusById($id,$status)
    {
        $date = date('Y-m-d',time());
        $row = array();                        
        $sql = "UPDATE `user_veso` SET `status` = '".$status."' WHERE `id` = ".$id." ";        
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);
        $row = $command->execute();            
        return $row;
    }
    
    public function getRowByUserName($user_name){
        $row = array();        
        $sql = "SELECT `id`, `username` FROM `user_veso` WHERE `username` = '".$user_name."'";            
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);            
        $row = $command->queryRow();                        
        return $row;    
    }
    
    public function getRowByKeyWord($key){
        $row = array();
        $str = "";
        if($key != ""){
            $str .= " AND `username` LIKE '%".$key."%'";    
        }        
        $sql = "SELECT `id`, `username` FROM `user_veso` WHERE 1 ".$str;          
        $connect = Yii::app()->db;
        $command = $connect->createCommand($sql);            
        $row = $command->queryAll();                        
        return $row;        
    }
}
